
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/21/2012 02:50:21
-- Generated from EDMX file: C:\Users\erick.avilaq\Dropbox\Proyectos\MarcatelHD\MarcatelHD\Models\Modelo.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [marcateldb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_tblReportes_tblServicios]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tblReportes] DROP CONSTRAINT [FK_tblReportes_tblServicios];
GO
IF OBJECT_ID(N'[dbo].[FK_tblReportes_tblSoluciones]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tblReportes] DROP CONSTRAINT [FK_tblReportes_tblSoluciones];
GO
IF OBJECT_ID(N'[dbo].[FK_tblUsuarios_tblRoles]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tblUsuarios] DROP CONSTRAINT [FK_tblUsuarios_tblRoles];
GO
IF OBJECT_ID(N'[dbo].[FK_tblUsuarios_tblServicios]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tblUsuarios] DROP CONSTRAINT [FK_tblUsuarios_tblServicios];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[sysdiagrams]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sysdiagrams];
GO
IF OBJECT_ID(N'[dbo].[tblReportes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tblReportes];
GO
IF OBJECT_ID(N'[dbo].[tblRoles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tblRoles];
GO
IF OBJECT_ID(N'[dbo].[tblServicios]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tblServicios];
GO
IF OBJECT_ID(N'[dbo].[tblSoluciones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tblSoluciones];
GO
IF OBJECT_ID(N'[dbo].[tblUsuarios]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tblUsuarios];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'sysdiagrams'
CREATE TABLE [dbo].[sysdiagrams] (
    [name] nvarchar(128)  NOT NULL,
    [principal_id] int  NOT NULL,
    [diagram_id] int IDENTITY(1,1) NOT NULL,
    [version] int  NULL,
    [definition] varbinary(max)  NULL
);
GO

-- Creating table 'tblReportes'
CREATE TABLE [dbo].[tblReportes] (
    [ReporteId] int IDENTITY(1,1) NOT NULL,
    [NombreTitular] nvarchar(150)  NOT NULL,
    [Telefono] nvarchar(10)  NOT NULL,
    [ServicioId] int  NOT NULL,
    [Descripcion] nvarchar(200)  NOT NULL,
    [SolucionId] int  NULL
);
GO

-- Creating table 'tblRoles'
CREATE TABLE [dbo].[tblRoles] (
    [RolId] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tblServicios'
CREATE TABLE [dbo].[tblServicios] (
    [ServicioId] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL,
    [Ubicacion] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tblSoluciones'
CREATE TABLE [dbo].[tblSoluciones] (
    [SolucionId] int IDENTITY(1,1) NOT NULL,
    [Causa] nvarchar(100)  NOT NULL,
    [Descripcion] nvarchar(200)  NOT NULL
);
GO

-- Creating table 'tblUsuarios'
CREATE TABLE [dbo].[tblUsuarios] (
    [UsuarioId] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL,
    [ApMaterno] nvarchar(50)  NOT NULL,
    [ApPaterno] nvarchar(50)  NOT NULL,
    [Direccion] nvarchar(100)  NOT NULL,
    [Telefono] nvarchar(10)  NOT NULL,
    [Usuario] nvarchar(10)  NOT NULL,
    [Contrasena] nvarchar(10)  NOT NULL,
    [RolId] int  NOT NULL,
    [ServicioId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [diagram_id] in table 'sysdiagrams'
ALTER TABLE [dbo].[sysdiagrams]
ADD CONSTRAINT [PK_sysdiagrams]
    PRIMARY KEY CLUSTERED ([diagram_id] ASC);
GO

-- Creating primary key on [ReporteId] in table 'tblReportes'
ALTER TABLE [dbo].[tblReportes]
ADD CONSTRAINT [PK_tblReportes]
    PRIMARY KEY CLUSTERED ([ReporteId] ASC);
GO

-- Creating primary key on [RolId] in table 'tblRoles'
ALTER TABLE [dbo].[tblRoles]
ADD CONSTRAINT [PK_tblRoles]
    PRIMARY KEY CLUSTERED ([RolId] ASC);
GO

-- Creating primary key on [ServicioId] in table 'tblServicios'
ALTER TABLE [dbo].[tblServicios]
ADD CONSTRAINT [PK_tblServicios]
    PRIMARY KEY CLUSTERED ([ServicioId] ASC);
GO

-- Creating primary key on [SolucionId] in table 'tblSoluciones'
ALTER TABLE [dbo].[tblSoluciones]
ADD CONSTRAINT [PK_tblSoluciones]
    PRIMARY KEY CLUSTERED ([SolucionId] ASC);
GO

-- Creating primary key on [UsuarioId] in table 'tblUsuarios'
ALTER TABLE [dbo].[tblUsuarios]
ADD CONSTRAINT [PK_tblUsuarios]
    PRIMARY KEY CLUSTERED ([UsuarioId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ServicioId] in table 'tblReportes'
ALTER TABLE [dbo].[tblReportes]
ADD CONSTRAINT [FK_tblReportes_tblServicios]
    FOREIGN KEY ([ServicioId])
    REFERENCES [dbo].[tblServicios]
        ([ServicioId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tblReportes_tblServicios'
CREATE INDEX [IX_FK_tblReportes_tblServicios]
ON [dbo].[tblReportes]
    ([ServicioId]);
GO

-- Creating foreign key on [SolucionId] in table 'tblReportes'
ALTER TABLE [dbo].[tblReportes]
ADD CONSTRAINT [FK_tblReportes_tblSoluciones]
    FOREIGN KEY ([SolucionId])
    REFERENCES [dbo].[tblSoluciones]
        ([SolucionId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tblReportes_tblSoluciones'
CREATE INDEX [IX_FK_tblReportes_tblSoluciones]
ON [dbo].[tblReportes]
    ([SolucionId]);
GO

-- Creating foreign key on [RolId] in table 'tblUsuarios'
ALTER TABLE [dbo].[tblUsuarios]
ADD CONSTRAINT [FK_tblUsuarios_tblRoles]
    FOREIGN KEY ([RolId])
    REFERENCES [dbo].[tblRoles]
        ([RolId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tblUsuarios_tblRoles'
CREATE INDEX [IX_FK_tblUsuarios_tblRoles]
ON [dbo].[tblUsuarios]
    ([RolId]);
GO

-- Creating foreign key on [ServicioId] in table 'tblUsuarios'
ALTER TABLE [dbo].[tblUsuarios]
ADD CONSTRAINT [FK_tblUsuarios_tblServicios]
    FOREIGN KEY ([ServicioId])
    REFERENCES [dbo].[tblServicios]
        ([ServicioId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tblUsuarios_tblServicios'
CREATE INDEX [IX_FK_tblUsuarios_tblServicios]
ON [dbo].[tblUsuarios]
    ([ServicioId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------